package bd.hsqldb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;

/**
 * Carga y configura la conexión a la base de datos.
 * @author Esther García Galán
 * @author Rubén Gómez Vales
 * @version 1.0 13/05/2014
 */
public class BDOcio {

    static Connection con = null;
    static Statement st = null;  
    static PreparedStatement pst = null;
    static ResultSet res = null; 
    static String sql  = null;
    private static final String DBH = "jdbc:hsqldb:mem:ocio";
    
    private static boolean BDcargada = true;
    
    static {

    	loadHSQLDB();
        connectDB();
        createStatement();
        crearTablas();
        cargarDatos();     
}

    /**
 	 * Carga del driver jdbc de la base de datos.              
 	 */
     public static void loadHSQLDB()
     {
         System.out.println("* Starting...");
         try
         {
             Class.forName("org.hsqldb.jdbcDriver"); //Load HSQLDB driver
             System.out.println("* Loading HSQLDB driver...");
         } 
         catch(Exception e)
         {  
             System.err.println("ERROR: failed to load HSQLDB JDBC driver.");
             e.printStackTrace();
             setBDcargada(false);
             return;  
         } 
     }

     /**
 	 * Establece la conexión con la base de datos. En caso de no existir, la base de datos es creada.               
 	 */    
     public static void connectDB()
     {
    	 System.out.println("* ConnectDB...");
         try
         { 
             con = DriverManager.getConnection(DBH); 
             System.out.println("* Creating HSQLDB connection...");
         }
         catch(Exception e)
         {
             System.err.println("Error: " + e.getMessage());
             setBDcargada(false);
             return;
         }
     }
     /**
 	 * Crea sobre la conexión un objeto Statement para poder 
 	 * enviar sentencias sql a la base de datos.             
 	 */
     public static void createStatement(){
    	 System.out.println("* CreateStatement...");
         try{
             st  = con.createStatement(); 
         }
         catch (Exception e) {  
             System.err.println("Error: createStatement: " + e.getMessage());   
             setBDcargada(false);
             return;
         }
     }

     /**
 	 * Crea las tablas de la base de datos.             
 	 */
     public static void crearTablas(){
    	   try{   
             
           	sql = "CREATE TABLE provincia (" + 
 				 	 "id         	INTEGER PRIMARY KEY, "  +
 				 	 "nombre		VARCHAR(50)	"			+
                      ")";
          	st.executeUpdate(sql);  


          	sql = "CREATE TABLE subtipo (" + 
				 	 "id         	INTEGER PRIMARY KEY, "  +
				 	 "nombre		VARCHAR(50),"			+
                     "tipo 			INTEGER NOT NULL"		+
				 	 ")";
         	st.executeUpdate(sql);  


          	
          	
        	 sql = "CREATE TABLE usuario (" +
				 	 "id         	INTEGER PRIMARY KEY, "  +
        			 "nombre		VARCHAR(50) NOT NULL, " +
                     "correo       	VARCHAR(50) NOT NULL, " +
                     "password		VARCHAR(20) NOT NULL, " +
                     "rol			INTEGER NOT NULL, " + //1-admin, 2-cliente, 3-proveedor.
                     "baja			BOOLEAN, " +
                     "fechabaja     DATE)";  
          	st.executeUpdate(sql);  


         	sql = "CREATE TABLE cliente (" +
                     "idUsuario  		INTEGER PRIMARY KEY, " +
                     "telefono 			VARCHAR(15), " +
                     "FOREIGN KEY (idUsuario) REFERENCES usuario (id) ON DELETE CASCADE " +
                     ")";
         	st.executeUpdate(sql);  


         	sql = "CREATE TABLE admin (" +
                     "idUsuario	 INTEGER PRIMARY KEY, "  +
                     "FOREIGN KEY (idUsuario) REFERENCES usuario (id) ON DELETE CASCADE " +
         		     ")";
                     st.executeUpdate(sql);  
 

         	sql = "CREATE TABLE proveedor (" +
				 	"idUsuario  INTEGER PRIMARY KEY, " +
				 	"tipo           VARCHAR(30), "  +
				 	"NIF         	VARCHAR(10), "  +
				 	"telefono      	VARCHAR(15), "  +
                     "FOREIGN KEY (idUsuario) REFERENCES usuario (id) ON DELETE CASCADE " +
                     ")";
         	st.executeUpdate(sql);  


         	
            sql = "CREATE TABLE producto (" +
				    "id         	INTEGER PRIMARY KEY, "  +
				    "idProveedor	INTEGER, " +		//CLAVE EXTERNA
				    "idAdmin		INTEGER, " +		//CLAVE EXTERNA
				    "nombre       	VARCHAR(50), " +
                    "tipo 		   	INTEGER, " +                                            
                    "idSubtipo	   	INTEGER, " +                                            
                    "descripcion    VARCHAR(200), " +
                    "direccion    	VARCHAR(50), " +
                    "telefono	    VARCHAR(15), " +
                    "foto           VARCHAR(50), " +
                    "idProvincia	INTEGER, " + //CLAVE EXTERNA
                    "municipio		VARCHAR(20), " +
        		     "FOREIGN KEY (idProvincia) REFERENCES provincia (id) ON DELETE CASCADE, " +
           		      ")";
        	st.executeUpdate(sql);  


          	sql = "CREATE TABLE oferta (" +
				    "id         	INTEGER PRIMARY KEY, "  +
                    "nombre       	VARCHAR(50) NOT NULL, " +
                    "idProducto		INTEGER NOT NULL," + 	//FOREIGN KEY	
                    "plazasDisp 	INTEGER NOT NULL, " +
                    "plazasReserv	INTEGER NOT NULL, " +
                    "fechaLim	    DATE NOT NULL, " +
                     "precioXpersona	DOUBLE NOT NULL, " +
                    "descripcion    VARCHAR(200), " +
                    "FOREIGN KEY (idProducto) REFERENCES producto (id) ON DELETE CASCADE" +
        		    ")";  
          	st.executeUpdate(sql);  


          	sql = "CREATE TABLE reserva (" + 
				 	"id         	INTEGER PRIMARY KEY, "  +
				 	"numPlazas     	INTEGER, "  +
				 	"fecha         	DATE, "  +
				 	"idCliente		INTEGER, " +		//CLAVE EXTERNA
				    "idAdmin		INTEGER, " +		//CLAVE EXTERNA
				    "idOferta	 	INTEGER" +
        		    ")";
         	st.executeUpdate(sql);  


          	sql = "CREATE TABLE subscripcion (" + 
				 	 "id         	INTEGER PRIMARY KEY, "  +
				 	 "idCliente		INTEGER, "	+
				 	 "idAdmin		INTEGER, "	+
				 	 "tipo		  	INTEGER, " 	+     //1,2,3
                     "idSubtipo		INTEGER, "	+
                     "idProvincia 	INTEGER NOT NULL, "	+
                     ")";
         	st.executeUpdate(sql);  


         } 
         catch (Exception e){  
             System.err.println("Error: CREATE TABLE: " + e.getMessage());
             setBDcargada(false);
         }

     }

     /**
  	 * Carga las tablas de la base de datos con los datos de prueba            
  	 */
     public static void cargarDatos(){
 	    try{   
	    	//ID, NOMBRE, CORREO, PASSWORD, ROL, BAJA, FECHABAJA
          	sql = "INSERT INTO USUARIO VALUES(1,'Estrella Lucero Alba','admin@gmail.com','admina',1,'False',null)";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO USUARIO VALUES(2,'Samuel Vales', 'cliente@gmail.com','cliente',2,'False',null)";
 	    	st.executeUpdate(sql);  
 	    	sql = "INSERT INTO USUARIO VALUES(4,'Miguel Bach','otrocliente@gmail.com','cliente',2,'False',null)";
 	    	st.executeUpdate(sql);  
  	    	sql = "INSERT INTO USUARIO VALUES(3,'Alberto García','prov1@gmail.com','proveedor',3,'False',null)";
 	    	st.executeUpdate(sql);  
  	    	sql = "INSERT INTO USUARIO VALUES(5,'Rubén Gañán','prov2@gmail.com','proveedor',3,'False',null)";
 	    	st.executeUpdate(sql);  
  	    	sql = "INSERT INTO USUARIO VALUES(6,'Africa Arribas','prov3@gmail.com','proveedor',3,'False',null)";
 	    	st.executeUpdate(sql);  

 	    	
 	    	sql = "INSERT INTO ADMIN VALUES(1)";
 	    	st.executeUpdate(sql);  
 
 	    	
 	    	//IDUSUARIO, TELEFONO
 	    	sql = "INSERT INTO CLIENTE VALUES(2,'555-987654')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO CLIENTE VALUES(4,'555-123456')";
 	    	st.executeUpdate(sql);  

 	    	
 	    	//IDUSUARIO, TIPOACTIVIDAD, NIF, TELEFONO
 	    	sql = "INSERT INTO PROVEEDOR VALUES(3,1,'22233344-P','659.123.456')"; //Tipo restaurantes
 	    	st.executeUpdate(sql);  
 	    	sql = "INSERT INTO PROVEEDOR VALUES(5,2,'22233345-P','659.123.457')"; //Tipo espectáculos
 	    	st.executeUpdate(sql);  
 	    	sql = "INSERT INTO PROVEEDOR VALUES(6,3,'22233346-P','659.123.458')"; //Tipo actividades
 	    	st.executeUpdate(sql);  
 	    
 	
 	    	sql = "INSERT INTO PROVINCIA VALUES(0,'Todas las Provincias')";
 	    	st.executeUpdate(sql);  
	    	sql = "INSERT INTO PROVINCIA VALUES(1,'Madrid')";
 	    	st.executeUpdate(sql);  
	    	sql = "INSERT INTO PROVINCIA VALUES(2,'Barcelona')";
 	    	st.executeUpdate(sql);  
	    	sql = "INSERT INTO PROVINCIA VALUES(3,'Asturias')";
 	    	st.executeUpdate(sql);  
	    	sql = "INSERT INTO PROVINCIA VALUES(5,'Granada')";
 	    	st.executeUpdate(sql);  
	    	sql = "INSERT INTO PROVINCIA VALUES(6,'Valencia')";
 	    	st.executeUpdate(sql);  
	    	sql = "INSERT INTO PROVINCIA VALUES(7,'Pontevedra')";
 	    	st.executeUpdate(sql);  
 	    	
 	    	//Id, nombre, TIPOACTIVIDAD
 	    	sql = "INSERT INTO SUBTIPO VALUES(1,'Asturiano',1)";
 	    	st.executeUpdate(sql);  
	    	sql = "INSERT INTO SUBTIPO VALUES(2,'Italiano',1)";
 	    	st.executeUpdate(sql);  
	    	sql = "INSERT INTO SUBTIPO VALUES(3,'Brasileño',1)";
 	    	st.executeUpdate(sql);  
	    	sql = "INSERT INTO SUBTIPO VALUES(4,'Concierto Pop-Rock',2)";
 	    	st.executeUpdate(sql);  
	    	sql = "INSERT INTO SUBTIPO VALUES(5,'Ópera',2)";
 	    	st.executeUpdate(sql);  
	    	sql = "INSERT INTO SUBTIPO VALUES(6,'Danza',2)";
 	    	st.executeUpdate(sql);  
	    	sql = "INSERT INTO SUBTIPO VALUES(7,'Teatro',2)";
 	    	st.executeUpdate(sql);  
	    	sql = "INSERT INTO SUBTIPO VALUES(8,'Spa-Relax',3)";
 	    	st.executeUpdate(sql);  
	    	sql = "INSERT INTO SUBTIPO VALUES(9,'Aventura',3)";
 	    	st.executeUpdate(sql);  
 	    	sql = "INSERT INTO SUBTIPO VALUES(10,'Música Sacra',2)";
 	    	st.executeUpdate(sql);  
 	 	    	
 	    	//ID, IDPROVEEDOR, IDADMIN, NOMBRE, TIPOACTIVIDAD, IDSUBTIPOACTIVIDAD, DESCRIPCION, 
 	    	//DIRECCION, TELEFONO, FOTO, IDPROVINCIA, MUNICIPIO
 
 	    	sql = "INSERT INTO PRODUCTO VALUES(1,5,null,'La Boheme, Teatro Gran Vía',2,5," +
 	    			"'Una de las tres grandes obras del maestro Puccini de nuevo en el teatro Gran Vía'," +
 	    			"'Calle Gran Vía 38','91-786 54 23','1.jpg',1,'Alcala de Henares')";
 	    	st.executeUpdate(sql);  
 	    	sql = "INSERT INTO PRODUCTO VALUES(2,5,null,'Requiem de Mozart, Auditorio Nacional',2,10," +
 	    			"'La orquesta de Radio Nacional y el coro de Voces de Madrid interpretan la inmortal obra de Mozart'," +
 	    			"'Príncipe de Vergara 72','91-586 54 23','2.jpg',1,'Madrid')";
 	    	st.executeUpdate(sql);  
 	    	sql = "INSERT INTO PRODUCTO VALUES(3,5,null,'Concierto de Pablo Alborán',2,4," +
 	    			"'Concierto de Pablo Alborán para un aforo reducido de 300 personas en el Café Y Más'," +
 	    			"'Calle de Enmedio 17','91-777 54 27','3.jpg',1,'Madrid')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO PRODUCTO VALUES(4,6,null,'SPA Aguas de la Sierra',3,8," +
 	    			"'Centro de Relajación Integral. Masajes y Aguas Termales.'," +
 	    			"'Calle del Río 2','91-796 54 23','4.jpg',1,'Guadarrama')";
 	    	st.executeUpdate(sql);  
 	    	sql = "INSERT INTO PRODUCTO VALUES(5,6,null,'MultiAventura VolandoVoy',3,9," +
 	    			"'Bautismo Aéreo en la Sierra de Madrid'," +
 	    			"'Calle de las Nubes 4','91-716 54 23','5.jpg',1,'Cercedilla')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO PRODUCTO VALUES(6,3,null,'Restaurante El Escalopín',1,1," +
 	    			"'Comida casera asturiana en el centro de Barcelona'," +
 	    			"'Calle La Rambla 38','93-786 54 23','6.jpg',2,'Barcelona')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO PRODUCTO VALUES(7,3,null,'La vieja Trattoria',1,2," +
 	    			"'Comida tradicional italiana'," +
 	    			"'Calle Mayor 39','91-786 56 23','7.jpg',1,'Getafe')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO PRODUCTO VALUES(8,5,null,'La Traviata, Teatro Nuevo Alcalá',2,5," +
 	    			"'Si nunca has ido a la ópera deberías empezar con ésta, te emocionará.'," +
 	    			"'Calle Gran Vía 38','91-786 54 23','8.jpg',1,'Madrid')";
 	    	st.executeUpdate(sql);  
 	    	sql = "INSERT INTO PRODUCTO VALUES(9,5,null,'El Mesías de Haendel, Teatro Monumental',2,10," +
 	    			"'La Joven Orquesta y Coro Nacional de España interpretan la inolvidable obra del maestro Haendel.'," +
 	    			"'Atocha 35','91-555 54 12','9.jpg',1,'Madrid')";
 	    	st.executeUpdate(sql);  
 	    	sql = "INSERT INTO PRODUCTO VALUES(10,5,null,'Presuntos Implicados en concierto',2,4," +
 	    			"'No te pierdas este concierto extraordinario, los componentes del grupo reunidos para esta ocasión especial.'," +
 	    			"'Palacio de los Deportes. C/Goya 17','91-777 11 22','10.jpg',1,'Madrid')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO PRODUCTO VALUES(11,6,null,'SPA Sal de Mar',3,8," +
 	    			"'Jacuzzi, piscina de agua dulce y de agua salada, sauna.'," +
 	    			"'Calle del mar 3','91-796 54 23','11.jpg',1,'Pinto')";
 	    	st.executeUpdate(sql);  
 	    	sql = "INSERT INTO PRODUCTO VALUES(12,6,null,'MultiAventura Entre Pinos',3,9," +
 	    			"'Haz una ruta... pero no por el suelo.'," +
 	    			"'La Barranca s/n','91-716 54 23','12.jpg',1,'Navacerrada')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO PRODUCTO VALUES(13,3,null,'Restaurante Brasilia',1,3," +
 	    			"'Ven a degustar los sabores de Brasil en Barcelona.'," +
 	    			"'Paseo del Mediterráneo 124','93-543 54 23','13.jpg',2,'Barcelona')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO PRODUCTO VALUES(14,3,null,'Jamón y más',1,1," +
 	    			"'El lugar perfecto para tomar unas tapas con cerveza con tus amigos.'," +
 	    			"'Calle Sol 39','91-786 56 23','14.jpg',1,'Parla')";
 	    	st.executeUpdate(sql);  
	    	
		    //ID, NOMBRE, IDPRODUCTO, PLAZASDISP, PLAZASRESERV, FECHALIM, 
 	    	//PRECIOXPERSONA, DESCRIPCION

 	    	sql = "INSERT INTO OFERTA VALUES(1,'Descuento en patio de butacas',1,30,8,'2014-9-3',10," +
 	    			"'Descuento del 50 en localidades de patio de butacas')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO OFERTA VALUES(2,'Segunda entrada gratis.',1,15,3,'2014-5-21',20," +
 	    			"'Por la compra de cada entrada otra gratis para que vayas con quien tú quieras')";
 	    	st.executeUpdate(sql);
   	    	sql = "INSERT INTO OFERTA VALUES(3,'Descuento 30',2,10,8,'2014-10-15',45," +
 	    			"'Descuento del 30 en entrada general')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO OFERTA VALUES(4,'Entrada Vip',3,4,2,'2014-8-15',50," +
 	    			"'Entrada Vip al mismo precio que la general. Conoce al artista en persona')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO OFERTA VALUES(5,'Menu degustación a 30 Euros',6,20,0,'2014-9-17',30," +
 	    			"'Fabes, Escalopines al Cabrales, Paté de Oricio y 6 platos más a un precio increíble.')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO OFERTA VALUES(6,'Pizzas a 6 Euros',7,2000,0,'2014-10-31',6," +
 	    			"'Elige tu pizza favorita por solo 6 euros.')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO OFERTA VALUES(7,'Circuito termal de 3 horas',4,5,4,'2014-7-21',35," +
 	    			"'Circuito de tres horas en nuestras piscinas por tan sólo 35 Euros.')";
 	    	st.executeUpdate(sql);
 	    	
 	    	sql = "INSERT INTO OFERTA VALUES(8,'Menu para dos',13,25,0,'2014-8-20',25," +
 	    			"'Disfruta nuestras carnes a la parrilla con quien más quieras.')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO OFERTA VALUES(9,'Tapa y caña a 3 euros.',14,2000,0,'2014-07-31',3," +
 	    			"'Tapa de jamón y caña de cerveza.')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO OFERTA VALUES(10,'Circuito relax 1 hora.',11,10,4,'2014-7-15',35," +
 	    			"'Circuito de una hora. Para olvidar las prisas.')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO OFERTA VALUES(11,'Circuito entre pinos. Disfruta de la naturaleza.',12,10,0,'2014-09-30',95," +
 	    			"'Equipo necesario incluido en el precio.')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO OFERTA VALUES(12,'Descuento en platea.',9,15,8,'2014-9-3',10," +
 	    			"'Butacas en platea a mitad de precio.')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO OFERTA VALUES(13,'3x2 en entradas de patio de butacas.',8,15,3,'2014-5-21',20," +
 	    			"'Por la compra de dos entradas, regalo de una tercera.')";
 	    	st.executeUpdate(sql);
   	    	sql = "INSERT INTO OFERTA VALUES(14,'Descuento carnet joven.',9,10,8,'2014-10-15',6," +
 	    			"'Mostrando el carnet joven, entrada a 6 euros.')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO OFERTA VALUES(15,'Venta anticipada.',10,4,2,'2014-8-15',25," +
 	    			"'No esperes al día del concierto, precio especial por venta anticipada.')";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO OFERTA VALUES(16,'Dos saltos en Paracaídas por 95 Euros',5,10,0,'2014-12-31',95," +
 	    			"'Dos saltos al precio de uno y si no se abre el paracaídas le devolvemos su dinero')";
 	    	st.executeUpdate(sql);
 	    	
 	    	
 
	    	//ID, NUMPLAZAS, FECHA, IDCLIENTE, IDADMIN, IDOFERTA
 	    	sql = "INSERT INTO RESERVA VALUES(1,2,'2014-9-3',2,null,1)";
 	    	sql = "INSERT INTO RESERVA VALUES(3,2,'2014-5-21',2,null,2)";
 	    	st.executeUpdate(sql);
 	    	sql = "INSERT INTO RESERVA VALUES(2,4,'2014-10-15',2,null,3)"; 	    		
	    	st.executeUpdate(sql);
	    	sql = "INSERT INTO RESERVA VALUES(4,2,'2014-05-01',2,null,11)"; 	    		
	    	st.executeUpdate(sql);
 	    	
 	    	//ID, IDCLIENTE, IDADMIN, TIPO, IDSUBTIPO, IDPROVINCIA.
 	    	sql = "INSERT INTO SUBSCRIPCION VALUES(1,2,null,2,4,1)"; 	    		
	    	st.executeUpdate(sql);
 	    	} 
          catch (Exception e){  
              System.err.println("Error introduciendo datos en la BBDD: " + e.getMessage());
              setBDcargada(false);
          }
 
  } 



     /**
 	 * Salva los datos temporales y cierra la conexión con la base de datos.           
 	 */    
     public static void closeHSQLDB(){
    	 System.out.println("* Cerrar BD...");
         try{
             st = con.createStatement();  
             st.executeUpdate("SHUTDOWN");  
             st.close(); 
         }
         catch(Exception e){
             System.out.println("Error: save temporal data" + e.getMessage());
         }
         
         try{
             con.close();
         }
         catch(Exception e){
             System.out.println("Error: " + e.getMessage());
             e.printStackTrace();
             return; 
         }
     }

	public static boolean isBDcargada() {
		return BDcargada;
	}

	public static void setBDcargada(boolean bDcargada) {
		BDcargada = bDcargada;
	}

}
